Click Here!
home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96

Bookmark It

Search this book:
 
Previous Table of Contents Next


Notice that there are three user SQL statements defined in the output from SQL Trace (Listing 25.1) but only one SQL statement is shown. This is because the Oracle commands used by the other session to enable SQL Trace are included in the output. For now, focus on the output related to the following SQL statements (the output follows the SQL statements):

SELECT
    SUBSTR(dogname,1,20) "Dog Name",
     SUBSTR(breed_name,1,20) "Breed",
    SUBSTR(owner,1,20) "Owner"
FROM
    dogs, breeds
WHERE
    dogs.breed = breeds.breed
ORDER BY
    dogs.breed

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.57          2          0          4           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.08          2          2          6          25
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.65          4          2         10          25

Misses in library cache during parse: 1
Optimizer hint: CHOOSE
Parsing user id: 10  (ED)

From this output, you can see that the majority of the elapsed time was spent in the parse phase. The CPU time to execute this statement was less than 0.01 second (which is the resolution of timed statistics). The following chart describes these statistics in a little more detail.


Parameter Value and Meaning

count The count value is 1, 1, and 2 (Parse, Execute, and Fetch), indicating that the SQL statement executed an OCI call once during the parse, again in the execute phases, and twice during the fetch.
cpu The CPU time spent in each of the phases was 0.0, 0.0, and 0.0 seconds (Parse, Execute, and Fetch). These minuscule times are the result of the tiny size of the database tables and the query. Your SQL statements will probably use much more CPU time.
elapsed The elapsed time. By far, the majority of the time executing this SQL statement is in the parse phase. However, this is not true for more complex statements and larger tables.
disk Notice that it is only during the parse and fetch phases that there is any disk activity.
query Because this SQL statement is a query, the fetches are done in consistent mode rather than current mode.
current The reads for the parsing phase and some of the reads for the fetching phase were done in current mode. Reads are done in current mode whenever possible.
rows The fetch phase processed 25 rows. Because this is a query operation, this value indicates how many rows were returned. In an UPDATE, INSERT, or DELETE operation, the rows parameter in the fetch phase would indicate how many rows were processed in each of these statements.

This information can give you valuable insight into how your queries are running. Although the output for the EXPLAIN PLAN command was also given here, it is described later in this chapter.

In addition to the preceding information about the SQL statements statistics, SQL Trace also provides the following information about library cache statistics and optimizer hints:

Misses in library cache during parse: 1
Optimizer hint: CHOOSE
Parsing user id: 10 (ED)

With this particular SQL statement, there was 1 library cache miss (which is quite high, considering that only one SQL statement was executed). The Optimizer hint: CHOOSE line indicates that the optimization method was left to Oracle to decide.

Also of importance are any recursive SQL statements that Oracle has to execute on behalf of the user’s SQL statement. In this example, I used TKPROF with the parameter SYS=NO to prevent the reporting of recursive statements. I did that because I was manually turning SQL Trace on and off and did not want to show a lot of irrelevant output. I also knew that the sample SQL statement would not generate any recursive calls.

I recommend that you do not set SYS=NO unless you are in a similar situation. It is very useful to obtain an indication of any recursive calls being executed. Later in this chapter, you use EXPLAIN PLAN to get an idea of how the SQL statement has been executed.

Review of SQL Trace

The first part of this chapter looked at SQL Trace, a very powerful facility provided by Oracle for debugging SQL performance problems. SQL Trace can provide much valuable information you can use to debug many different types of performance problems.

SQL Trace provides valuable information on such things as these:

  Parse, execute, and fetch counts
  CPU and elapsed times
  Physical and logical reads
  Number of rows processed
  Library cache misses

The EXPLAIN PLAN Command

The EXPLAIN PLAN command shows you the execution plan that the Oracle optimizer has chosen for your SQL statements. With this information, you can determine whether the Oracle optimizer has chosen the correct execution plan based on your knowledge of the data and the application. You can also use EXPLAIN PLAN to determine whether any additional optimization should be done to your database (for example, the addition of an index or the use of a cluster).

The EXPLAIN PLAN command is used to display the execution plan chosen by the Oracle optimizer for SELECT, UPDATE, INSERT, and DELETE statements. After using EXPLAIN PLAN, you can rewrite your SQL statements and see whether the new SQL statement is better optimized than the original statement. By analyzing the output, you may be able to provide hints that the Oracle optimizer can use to take better advantage of the data (hints are described in Chapter 30, “Using Hints”). By using hints, you can take better advantage of features such as the Oracle Parallel Query option.


Previous Table of Contents Next


Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited.